import xlwt
from datetime import datetime


def set_style(name, height, bold=False, format_str=""):
    style = xlwt.XFStyle()  # 初始化样式

    font = xlwt.Font()  # 为样式创建字体
    font.name = name
    font.bold = bold
    font.height = height

    borders = xlwt.Borders()  # 为样式创建边框
    borders.left = 6
    borders.right = 6
    borders.top = 6
    borders.bottom = 6

    style.font = font
    style.borders = borders
    style.num_format_str = format_str
    return style


# 创建一个excel文件对象
wb = xlwt.Workbook()
# 添加一个sheet
# 参数cell_overwrite_ok，通常设置为True，这个参数的意义在于，如果对一个单元格重复操作，需要将这个参数设置为True
sheet1 = wb.add_sheet("学生", cell_overwrite_ok=True)
sheet2 = wb.add_sheet("成绩", cell_overwrite_ok=True)
row0 = ["姓名", "年龄", "出生日期", "爱好"]
colum0 = ["张三", "李四", "练习Python", "小明", "小红", "无名"]
# 写第一行
for i in range(0, len(row0)):
    sheet1.write(0, i, row0[i], set_style("Times New Roman", 220, True))

style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY'

# 写第一列
for i in range(0, len(colum0)):
    sheet1.write(i + 1, 0, colum0[i], set_style("Times New Roman", 220, True))
    sheet1.write(1, 3, "2006/12/12") # 第二行第四列被合并
    sheet1.write(1, 2, "2022/10/12") # 第二行第三列赋值
    sheet1.write(1, 6, datetime.now()) #被当做数字
    sheet1.write(1, 7, datetime.now(), style)# 写入datatime类型数据
    sheet1.write(1, 8, True) # 写入Boolean类型数据
    sheet1.write_merge(6, 6, 1, 3, '未知')  # 合并行单元格
    sheet1.write_merge(1, 2, 3, 3, '打游戏')  # 合并列单元格
    sheet1.write_merge(4, 5, 3, 3, '打篮球')
wb.save("test.xls")  # 保存到test.xls

ws = wb.add_sheet("A Test Sheet")
ws.col(0).width = 200 * 30
ws.write(0, 0, 1234.56, set_style('Times New Roman', 220, bold=True, format_str='#,##0.00'))
ws.write(1, 0, datetime.now(), set_style('Times New Roman', 220, bold=False, format_str='DD-MM-YYYY'))
styleOK = xlwt.easyxf('pattern: fore_colour light_blue;'
                      'font: colour green, bold True;')

# 设置表格样式
pattern = xlwt.Pattern()  # 一个实例化的样式类
pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 固定的样式
pattern.pattern_fore_colour = xlwt.Style.colour_map['red']  # 背景颜色
styleOK.pattern = pattern
ws.write(2, 0, 1, style=styleOK)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))
wb.save("example.xls")  # 保存xls
